



ALTER     PROCEDURE sp_get_auto_rma_number
AS

BEGIN
	DECLARE @TEMP_NUMBER VARCHAR(20)
	DECLARE @AUTO_NUMBER VARCHAR(20)
	DECLARE @MONTH_YEAR  VARCHAR(8)
	DECLARE @PREFIX      VARCHAR(50)
	DECLARE @LENGTH      INT

	--Added by Rohan on July 22nd 2004	
	SELECT @PREFIX = RMA_Prefix, @LENGTH = RMA_Length from Sales_Info 
	IF( @@ROWCOUNT = 0 )
	BEGIN
		SET @PREFIX = ''
		SET @LENGTH = 0
	END
	
	SET @MONTH_YEAR =  REPLICATE( 0 , 2 - LEN( MONTH( GETDATE() ) ) ) + CONVERT ( VARCHAR(2),MONTH( GETDATE() ) ) + CONVERT( VARCHAR(4), YEAR( GETDATE() ) ) + '-'

	SELECT 	@AUTO_NUMBER = ISNULL( MAX(RMA_NUMBER) , '-0') FROM RMA_TABLE WHERE RMA_NUMBER LIKE '%'+ @MONTH_YEAR +'%'
	--SELECT 	@AUTO_NUMBER = RMA_NUMBER FROM RMA_TABLE WHERE RMA_ID = ( SELECT MAX( RMA_ID ) FROM RMA_TABLE )

	IF( @@ROWCOUNT = 0 )
	BEGIN
		SET @TEMP_NUMBER = 1		
	END
	ELSE
	BEGIN
		IF( CHARINDEX( '-' , @AUTO_NUMBER, CHARINDEX( '-' , @AUTO_NUMBER ) + 1 ) = 0 )
		BEGIN
			--IF THE '-' IS EXISTING ONLY ONCE			
			SELECT @TEMP_NUMBER = RIGHT(@AUTO_NUMBER , LEN( @AUTO_NUMBER ) - CHARINDEX( '-' , @AUTO_NUMBER ) ) + 1 
			
		END
		ELSE
		BEGIN
						
			--IF THE '-' IS EXISTING TWICE
			SELECT @TEMP_NUMBER = RIGHT(@AUTO_NUMBER , LEN( @AUTO_NUMBER ) - CHARINDEX( '-' , @AUTO_NUMBER, CHARINDEX( '-' , @AUTO_NUMBER ) + 1 ) ) + 1 				
		END
	END

	
	IF( LEN( @TEMP_NUMBER ) < @LENGTH )
	BEGIN
		SET @TEMP_NUMBER = REPLICATE( 0 , @LENGTH - LEN( @TEMP_NUMBER ) ) + @TEMP_NUMBER
	END
	

	--SET @AUTO_NUMBER =  'RMA' + @MONTH_YEAR + @TEMP_NUMBER
	IF( @PREFIX = '' )
	BEGIN
		SET @AUTO_NUMBER =  @MONTH_YEAR + @TEMP_NUMBER
	END
	ELSE
	BEGIN
		SET @AUTO_NUMBER =  @PREFIX + '-' + @MONTH_YEAR + @TEMP_NUMBER	
	END

	SELECT @AUTO_NUMBER [RMA_NUMBER]
END









